package com.gem.mapper;

import com.gem.domain.TrainingOrder;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author Bravea❤ ·勇
 * Description: 实训订单mapper
 * create
 * Modified By:
 */
@Mapper
public interface TrainingOrderMapper {
    @Update("UPDATE training_order SET training_order_liveFee = #{money} WHERE training_order_id = 0")
    Integer update(String money);

    //插入
    @Insert("INSERT INTO training_order (training_order_name,training_order_sex,training_order_IDNumber,training_order_tel,training_order_school,training_order_education," +
            "training_order_trainingBase,training_order_payDate,training_order_payType,training_order_schoolSubsidy,training_order_subsidyState,training_order_liveFee,training_order_liveFeeCase," +
            "training_order_agencyFee,training_order_discountType,user_channel_id,user_consult_id,training_order_remarks) VALUES (#{trainingOrderName},#{trainingOrderSex}," +
            "#{trainingOrderIDNumber},#{trainingOrderTel},#{trainingOrderSchool},#{trainingOrderEducation},#{trainingOrderTrainingBase},#{trainingOrderPayDate},#{trainingOrderPayType}," +
            "#{trainingOrderSchoolSubsidy},#{trainingOrderSubsidyState},#{trainingOrderLiveFee},#{trainingOrderLiveFeeCase},#{trainingOrderAgencyFee},#{trainingOrderDiscountType}," +
            "#{userChannelId},#{userConsultId},#{trainingOrderRemarks})")
    boolean addTrainingOrder(TrainingOrder t);

    //查找所有
    @Select("SELECT t1.*,t2.consult_name FROM (SELECT tor.*,u.personname channel_name FROM `user` u, training_order tor WHERE u.uid=tor.user_channel_id ) t1, " +
            "(SELECT tor.training_order_id , u.personname consult_name FROM `user` u, training_order tor WHERE u.uid=tor.user_consult_id ) t2 " +
            "WHERE t1.training_order_id=t2.training_order_id AND t1.training_order_status>0 ORDER BY training_order_payDate DESC")
    List<TrainingOrder> selectAllTrainingOrder();

    //只查找所有市场的
    @Select("SELECT t1.*,t2.consult_name FROM (SELECT tor.*,u.personname channel_name,u.department FROM `user` u, training_order tor WHERE u.uid=tor.user_channel_id ) t1, " +
            "(SELECT tor.training_order_id , u.personname consult_name,u.department FROM `user` u, training_order tor WHERE u.uid=tor.user_consult_id ) t2 " +
            "WHERE t1.training_order_id=t2.training_order_id AND ((t1.department='市场' AND t1.channel_name != '无' ) OR t2.department='市场') AND t1.training_order_status>0 ORDER BY training_order_payDate DESC")
    List<TrainingOrder> selectAllTrainingOrderNoOther();

    //查找自己的
    @Select("SELECT t1.*,t2.consult_name FROM (SELECT tor.*,u.personname channel_name FROM `user` u, training_order tor WHERE u.uid=tor.user_channel_id ) t1, " +
            "(SELECT tor.training_order_id , u.personname consult_name FROM `user` u, training_order tor WHERE u.uid=tor.user_consult_id ) t2 " +
            "WHERE t1.training_order_id=t2.training_order_id AND (t1.channel_name=#{personname} OR t2.consult_name=#{personname}) AND t1.training_order_status>0 ORDER BY training_order_payDate DESC")
    List<TrainingOrder> selectMyTrainingOrder(String personname);

    //查询单个
    @Select("SELECT * FROM training_order WHERE training_order_id = #{training_order_id} AND training_order_status>0")
    TrainingOrder selectTrainingOrderById(String training_order_id);

    //修改
    @Update("UPDATE training_order SET training_order_name=#{trainingOrderName},training_order_sex=#{trainingOrderSex},training_order_IDNumber=#{trainingOrderIDNumber}, " +
            "training_order_tel=#{trainingOrderTel},training_order_school=#{trainingOrderSchool},training_order_education=#{trainingOrderEducation}, " +
            "training_order_trainingBase=#{trainingOrderTrainingBase},training_order_payDate=#{trainingOrderPayDate},training_order_payType=#{trainingOrderPayType}, " +
            "training_order_schoolSubsidy=#{trainingOrderSchoolSubsidy},training_order_subsidyState=#{trainingOrderSubsidyState},training_order_liveFee=#{trainingOrderLiveFee}, " +
            "training_order_liveFeeCase=#{trainingOrderLiveFeeCase},training_order_agencyFee=#{trainingOrderAgencyFee},training_order_discountType=#{trainingOrderDiscountType}, " +
            "user_channel_id=#{userChannelId},user_consult_id=#{userConsultId},training_order_remarks=#{trainingOrderRemarks} WHERE training_order_id=#{trainingOrderId} AND training_order_status>0")
    boolean modifyTrainingOrderById(TrainingOrder t);

    //  @Delete("DELETE FROM training_order WHERE training_order_id=#{training_order_id}")//真删
    //伪删除
    @Update("UPDATE training_order SET training_order_status=-1 WHERE training_order_id=#{training_order_id}")
    boolean deleteTrainingOrderById(Integer training_order_id);

    //查询带条件--所有
    @Select("SELECT t1.*,t2.consult_name FROM (SELECT tor.*,u.personname channel_name FROM `user` u, training_order tor WHERE u.uid=tor.user_channel_id ) t1, " +
            "(SELECT tor.training_order_id , u.personname consult_name FROM `user` u, training_order tor WHERE u.uid=tor.user_consult_id ) t2 " +
            "WHERE t1.training_order_id=t2.training_order_id AND training_order_name LIKE #{param1} AND training_order_idNumber LIKE #{param2} AND training_order_payDate LIKE #{param3} AND training_order_status>0 ORDER BY training_order_payDate DESC")
    List<TrainingOrder> searchTrainingOrder(String training_order_name, String training_order_idNumber, String str_training_order_payDate);

    //查询带条件--全部市场的
    @Select("SELECT t1.*,t2.consult_name FROM (SELECT tor.*,u.personname channel_name,u.department FROM `user` u, training_order tor WHERE u.uid=tor.user_channel_id ) t1, " +
            "(SELECT tor.training_order_id , u.personname consult_name,u.department FROM `user` u, training_order tor WHERE u.uid=tor.user_consult_id ) t2 " +
            "WHERE t1.training_order_id=t2.training_order_id AND ((t1.department='市场' AND t1.channel_name != '无' ) OR t2.department='市场') " +
            "AND training_order_name LIKE #{param1} AND training_order_idNumber LIKE #{param2} AND training_order_payDate LIKE #{param3} AND training_order_status>0 ORDER BY training_order_payDate DESC")
    List<TrainingOrder> searchTrainingOrderNoOther(String training_order_name, String training_order_idNumber, String str_training_order_payDate);

    //查询带条件--所有
    @Select("SELECT t1.*,t2.consult_name FROM (SELECT tor.*,u.personname channel_name FROM `user` u, training_order tor WHERE u.uid=tor.user_channel_id ) t1, " +
            "(SELECT tor.training_order_id , u.personname consult_name FROM `user` u, training_order tor WHERE u.uid=tor.user_consult_id ) t2 " +
            "WHERE t1.training_order_id=t2.training_order_id AND (t1.channel_name=#{param4} OR t2.consult_name=#{param4} ) AND training_order_name LIKE #{param1} AND training_order_idNumber LIKE #{param2} AND training_order_payDate LIKE #{param3} AND training_order_status>0 ORDER BY training_order_payDate DESC")
    List<TrainingOrder> searchMyTrainingOrder(String training_order_name, String training_order_idNumber, String str_training_order_payDate, String personname);

    //报表
    //查询所有渠道专员的id
    @Select("select DISTINCT user_channel_id from training_order where user_channel_id != 0 and training_order_status > 0")
    List<Integer> findMid();

    //渠道专员累计招生人数
    @Select("select count(0) from training_order where user_channel_id != 0 and user_channel_id = #{id} and training_order_status > 0")
    Integer countStu(@Param("id") Integer id);

    @Select("select count(0) from training_order where user_channel_id != 0 and user_channel_id = #{id} and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(training_order_payDate) and training_order_status > 0")
    Integer countStuWeek(@Param("id") Integer id);
}
